package com.servlet;

import net.sf.json.JSON;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import java.sql.*;
import java.util.*;

public class Login {
    static int id ;
    static final String url = "jdbc:mysql://1.12.241.180:3305/testsys?useUnicode=true&characterEncoding=utf-8&useSSL=false";
    static final String username = "root";//数据库名称
    static final String password = "123456";//访问数据库密码
    public static int login(String userId, String pwd) throws SQLException {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            //Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");// 加载数据库驱动，注册到驱动管理器
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url,username,password);// 创建Connection连接

            stmt = conn.createStatement();// 获取Statement(获得发送sql的对象)

            String sql = "select * from com_user where userId = '"+ userId+"'";

            rs = stmt.executeQuery(sql);	// 执行查询
            boolean bool =  rs.first();
            if(bool == false){
                rs.close();	 // 关闭ResultSet

                stmt.close(); // 关闭Statement

                conn.close();// 关闭Connection
                return 0;
            }


            String uname = rs.getString("userName");
            System.out.println("Login查询的用户密码："+ uname);
            if(pwd.equals(uname)){
                id =  rs.getInt(1);

                rs.close();	 // 关闭ResultSet

                stmt.close(); // 关闭Statement

                conn.close();// 关闭Connection

                return 1;
            }else {
                rs.close();	 // 关闭ResultSet

                stmt.close(); // 关闭Statement

                conn.close();// 关闭Connection
                return 10;
            }


        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }


    }





}
class test extends  Login{
    public static int LogId(){
        Login l =new test();
        int i = l.id;
        return i;
    }
}

class insertLog {
    static int id ;
    static final String url = "jdbc:mysql://1.12.241.180:3305/testsys?useUnicode=true&characterEncoding=utf-8&useSSL=false";
    static final String username = "root";//数据库名称
    static final String password = "123456";//访问数据库密码
    public static int insertlogin(String userId, String src1,String name1,String price) throws ClassNotFoundException, SQLException {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        int row = 0;

            //Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");// 加载数据库驱动，注册到驱动管理器
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url,username,password);// 创建Connection连接

            stmt = conn.createStatement();// 获取Statement(获得发送sql的对象)

            //String sql = "select * from com_user where userId = '"+ userId+"'";
            //String sql = " INSERT INTO com_shopping set userid ="+userId+",set shoppingName = '"+name1+"', set shoppingPrice = "+price+", set shoppingUrl =  '"+src1+"'";
            String sql = "INSERT INTO com_shopping(userid,shoppingName,shoppingPrice,shoppingUrl)"+" VALUES("+userId+",'"+name1+"',"+price+",'"+src1+"')";
            System.out.println("插入商品的SQL语句"+sql);
            // rs = stmt.executeQuery(sql);	// 执行查询
             row = stmt.executeUpdate(sql);

           // rs.close();	 // 关闭ResultSet

            stmt.close(); // 关闭Statement

            conn.close();// 关闭Connection

            return row;

    }
}

class selectLog {
    static int id ;
    static final String url = "jdbc:mysql://1.12.241.180:3305/testsys?useUnicode=true&characterEncoding=utf-8&useSSL=false";
    static final String username = "root";//数据库名称
    static final String password = "123456";//访问数据库密码
    public static int selectshopp(String userId, String name1) throws ClassNotFoundException, SQLException {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;


        //Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");// 加载数据库驱动，注册到驱动管理器
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection(url,username,password);// 创建Connection连接

        stmt = conn.createStatement();// 获取Statement(获得发送sql的对象)

        String sql = "select * from com_shopping where userid = '"+ userId+"' and shoppingName = '"+name1+"' ";
        //String sql = " INSERT INTO com_shopping set userid ="+userId+",set shoppingName = '"+name1+"', set shoppingPrice = "+price+", set shoppingUrl =  '"+src1+"'";
        //String sql = "INSERT INTO com_shopping(userid,shoppingName,shoppingPrice,shoppingUrl)"+" VALUES("+userId+",'"+name1+"',"+price+",'"+src1+"')";
        System.out.println("查询商品的SQL语句"+sql);
        // rs = stmt.executeQuery(sql);	// 执行查询
        rs = stmt.executeQuery(sql);
        boolean bool =  rs.first();
        if(bool == false){
            rs.close();	 // 关闭ResultSet

            stmt.close(); // 关闭Statement

            conn.close();// 关闭Connection
            return 0;
        }
            int shid = rs.getInt("id");
            int sum = rs.getInt("shoppingsum");
            int sumadd = sum+1;

            String sql1 = " UPDATE  com_shopping set shoppingsum = "+sumadd+ " where id = "+shid;
            System.out.println("更新商品数量SQL语句"+sql1);
            stmt.executeUpdate(sql1);
            rs.close();	 // 关闭ResultSet

            stmt.close(); // 关闭Statement

            conn.close();// 关闭Connection

            return 1;

    }

}

class selectIdLog {
    static int id ;
    static final String url = "jdbc:mysql://1.12.241.180:3305/testsys?useUnicode=true&characterEncoding=utf-8&useSSL=false";
    static final String username = "root";//数据库名称
    static final String password = "123456";//访问数据库密码
    public static JSONArray selectshopp(int userId) throws ClassNotFoundException, SQLException {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;


        //Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");// 加载数据库驱动，注册到驱动管理器
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection(url,username,password);// 创建Connection连接

        stmt = conn.createStatement();// 获取Statement(获得发送sql的对象)

        String sql = "select * from com_shopping where userid = '"+ userId+" ' ";
        //String sql = " INSERT INTO com_shopping set userid ="+userId+",set shoppingName = '"+name1+"', set shoppingPrice = "+price+", set shoppingUrl =  '"+src1+"'";
        //String sql = "INSERT INTO com_shopping(userid,shoppingName,shoppingPrice,shoppingUrl)"+" VALUES("+userId+",'"+name1+"',"+price+",'"+src1+"')";
        System.out.println("根据用户ID查询商品的SQL语句"+sql);
        // rs = stmt.executeQuery(sql);	// 执行查询
        rs = stmt.executeQuery(sql);

        //实例化json
        JSONObject jsonobj = new JSONObject();
        JSONArray jsonarray = new JSONArray();
        while (rs.next()){

            jsonobj.put("shoppingName",rs.getString("shoppingName"));
            jsonobj.put("shoppingPrice",rs.getString("shoppingPrice"));
            jsonobj.put("shoppingUrl",rs.getString("shoppingUrl"));
            jsonobj.put("shoppingsum",rs.getString("shoppingsum"));
            jsonarray.add(jsonobj);

        }

            System.out.println(jsonarray);
            rs.close();	 // 关闭ResultSet

            stmt.close(); // 关闭Statement

            conn.close();// 关闭Connection

            return jsonarray;

    }

}